package sql;

import java.sql.ResultSet;
import java.sql.SQLException;

import bean.*;

public class FinanceAccountLookUp 
{
	DataAccess dataAccess = new DataAccess();
	FinanceAccountBean financeBean = new FinanceAccountBean();
	
	public FinanceAccountBean getCustomerFinanceAccount(int customerId) throws ClassNotFoundException, SQLException
	{
		getCustomerCreditCardInfo(customerId);
		getCustomerPayPalInfo(customerId);	
		
		return financeBean;
	}

	private void getCustomerCreditCardInfo(int customerId) throws ClassNotFoundException, SQLException 
	{
		String query = "SELECT FA.CUSTOMER_ID, CC.ACCOUNT_ID AS CC_ACCOUNT_ID, CC.CREDIT_CARD_NUMBER, FA.BALANCE AS CC_BALANCE" +
		                " FROM CREDIT_CARD_ACCOUNT CC" +
		               " INNER JOIN FINANCE_ACCOUNT FA ON CC.ACCOUNT_ID = FA.ACCOUNT_ID" +
		               "   AND FA.CUSTOMER_ID = " +customerId;
		
		ResultSet rset = dataAccess.getSelectResult(query);
		
		while(rset.next())
		{
			financeBean.setCustomerId(rset.getInt(1));
			financeBean.setCreditCardAccountId(rset.getInt(2));
			financeBean.setCreditCardNumber(rset.getLong(3));
			financeBean.setCreditCardBalance(rset.getDouble(4));			
		}		
	}
	
	private void getCustomerPayPalInfo(int customerId) throws ClassNotFoundException, SQLException 
	{
		String query = "SELECT FA.CUSTOMER_ID, PP.ACCOUNT_ID AS PP_ACCOUNT_ID, PP.PAYPAL_EMAIL_ADDRESS, FA.BALANCE AS PP_BALANCE" +
		                " FROM PAYPAL_ACCOUNT PP" +
		               " INNER JOIN FINANCE_ACCOUNT FA ON PP.ACCOUNT_ID = FA.ACCOUNT_ID" +
		               "   AND FA.CUSTOMER_ID = " +customerId;

		ResultSet rset = dataAccess.getSelectResult(query);
		
		while(rset.next())
		{
			financeBean.setCustomerId(rset.getInt(1));
			financeBean.setPaypalAccountId(rset.getInt(2));
			financeBean.setPaypalEmailAddress(rset.getString(3));
			financeBean.setPaypalBalance(rset.getDouble(4));			
		}		
	}
}
